﻿
using B.S.InOut.Domain;
using Dapper;
using Microsoft.Data.SqlClient;

namespace B.S.InOut.Data
{
    // 产品仓储类，实现 IProductRepository 接口，使用 Dapper 与数据库交互11
    public class ProductRepository : IProductRepository
    {
        // 数据库连接字符串
        private readonly string _connectionString;

        // 构造函数，接收数据库连接字符串
        public ProductRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        // 插入产品到数据库的异步实现
        public async Task<int> InsertProductAsync(ProductModel product)
        {
            // 使用 using 语句确保数据库连接在使用后正确关闭和释放资源
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义插入 SQL 语句
                var sql = "INSERT INTO ProductModels (productName, manufacturerCode,internalCode,unitId,categoryId,upperLimit,lowerLimit,specification,price,wHouseId,wHouseTypeId,description,productNum,Supplie,Customer) VALUES (@productName, @manufacturerCode,@internalCode,@unitId,@categoryId,@upperLimit,@lowerLimit,@specification,@price,@wHouseId,@wHouseTypeId,@description,@productNum,@Supplie,@Customer); SELECT CAST(SCOPE_IDENTITY() as int);";
                // 执行插入操作并返回插入记录的 ID
                return await connection.ExecuteScalarAsync<int>(sql, product);
            }
        }

        // 根据 ID 获取单个产品的异步实现
        public async Task<ProductModel> GetProductByIdAsync(int productId)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义查询 SQL 语句
                var sql = "SELECT * FROM ProductModels WHERE productId = @productId";
                // 执行查询操作并返回单个产品
                return await connection.QueryFirstOrDefaultAsync<ProductModel>(sql, new { productId = productId });
            }
        }

        // 获取所有产品的异步实现
        public async Task<IEnumerable<ProductModel>> GetAllProductsAsync()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义查询所有产品的 SQL 语句
                var sql = "SELECT * FROM ProductModels";
                // 执行查询操作并返回所有产品
                return await connection.QueryAsync<ProductModel>(sql);
            }
        }

        // 更新产品信息的异步实现
        public async Task<int> UpdateProductAsync(ProductModel product)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义更新 SQL 语句
                var sql = "UPDATE ProductModels SET productName = @productName,manufacturerCode = @manufacturerCode,internalCode = @internalCode,unitId = @unitId,CategoryId = @CategoryId,upperLimit = @upperLimit,lowerLimit = @lowerLimit,specification = @specification,price = @price,wHouseId = @wHouseId,wHouseTypeId = @wHouseTypeId,description = @description,productNum=@productNum,Supplie=@Supplie,Customer=@Customer WHERE productId = @productId";
                // 执行更新操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, product);
            }
        }

        // 根据 ID 删除产品的异步实现
        public async Task<int> DeleteProductAsync(int productId)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义删除 SQL 语句
                var sql = "DELETE FROM ProductModels WHERE productId = @productId";
                // 执行删除操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, new { productId = productId });
            }
        }

        // 新增的多条件分页查询方法实现
        public async Task<IEnumerable<ProductModel>> GetProductsByConditionsAsync(string? ProductNum, string? ProductName, string? Category, int pageindex, int pageSize)
        {
          
            using (var connection = new SqlConnection(_connectionString))
            {
                // 构建基础的 SQL 查询语句
                var sql = "select * from  ProductModels where 1=1";
                var parameters = new DynamicParameters();

                // 如果产品名称不为空，添加名称查询条件
                if (!string.IsNullOrEmpty(ProductNum))
                {
                    sql += " AND ProductNum like @ProductNum";
                    parameters.Add("@ProductNum", $"%{ProductNum}%");
                }

                // 如果最低价格不为空，添加最低价格查询条件
                if (!string.IsNullOrEmpty(Category))
                {
                    sql += " AND Category = @Category";
                    parameters.Add("@Category", Category);
                }

                // 如果最高价格不为空，添加最高价格查询条件
                if (!string.IsNullOrEmpty(ProductName))
                {
                    sql += " AND ProductName like @ProductName";
                    parameters.Add("@ProductName", $"%{ProductName}%");
                }
                // 添加分页逻辑
                var offset = (pageindex - 1) * pageSize;
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", pageSize);
                parameters.Add("@pageindex", pageindex);



                // 执行查询并返回结果
                return await connection.QueryAsync<ProductModel>(sql, parameters);
            }
        }

        // 新增的批量删除方法实现
        public async Task<int> DeleteProductsAsync(IEnumerable<int> productIds)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 构建删除 SQL 语句，使用 IN 子句来指定要删除的产品 ID
                var sql = "DELETE FROM ProductModels WHERE productId IN @ProductIds";
                // 使用 Dapper 的 DynamicParameters 来传递参数
                var parameters = new DynamicParameters();
                parameters.Add("@ProductIds", productIds);

                // 执行删除操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, parameters);
            }
        }

        public async Task<IEnumerable<ProductModel>> GetProductModelsByIdsAsync(List<int> productIds)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 构建 IN 子句的参数化查询
                string inClause = string.Join(",", productIds.Select((id, index) => $"@Id{index}"));
                var sql = $@"
                    SELECT 
                        pm.ProductId,
                        pm.ProductNum,
                        pm.ProductName,
                        pm.ManufacturerCode,
                        pm.InternalCode,
                        pm.UnitId,
                        pm.CategoryId,
                        pm.UpperLimit,
                        pm.LowerLimit,
                        pm.Specification,
                        pm.Price,
                        pm.WHouseId,
                        pm.WHouseTypeId,
                        pm.Supplie,
                        pm.Customer,
                        pm.Description
                    FROM 
                        ProductModels pm
                    WHERE 
                        pm.ProductId IN ({inClause})";

                var parameters = new DynamicParameters();
                for (int i = 0; i < productIds.Count; i++)
                {
                    parameters.Add($"@Id{i}", productIds[i]);
                }

                return await connection.QueryAsync<ProductModel>(sql, parameters);
            }
        }
    }
}
